"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : 兼容M*数据库timestamp函数
Case Name   : timestamp类型输入格式及取值测试
Create At   : 2023.05.16
Owner       : @wan005
Description :
    1、创建表
    2、dolphin.sql_mode参数在默认严格模式下，分别插入插入最小值插入舍入小数后超最大值，
    插入最大值
    3、dolphin.sql_mode参数在非严格模式下，分别插入舍入后超最大值
    4、dolphin.sql_mode参数不含零值日期，插入含零值日期
    5、清理环境
Expect      :
    1、成功，默认带时区
    2、最小值成功，插入舍入小数后超最大值报错
    3、插入舍入后超最大值有告警并返回零值，最大值成功
    4、成功
    5、成功
History     :modified by @wan005,2023.05.16，增加不同参数值下取值表现，sql展示的
            结果不显示时区，修改用例为py
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class CompatibilityTest01(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.sh_primary = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = "db_timestamp_0001"
        self.tb_name = "tb_timestamp_0001"

    def test_datatype(self):
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info('----前置条件：创建兼容b库---')
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} dbcompatibility 'b';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res)

        text = '--step1:创建表;expect:成功--'
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name};" \
                  f"create table {self.tb_name}(ts timestamp);"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      '执行失败:' + text)

        text = '--step2:dolphin.sql_mode参数在默认严格模式下，分别插入插入最小值，' \
               '插入舍入小数后超最大值，插入最大值;' \
               'expect:最小值成功，插入舍入小数后超最大值报错--'
        self.log.info(text)
        sql_cmd = f"insert {self.tb_name} values" \
                  f"('0001-01-01 00:00:00.000000');" \
                  f"insert {self.tb_name} " \
                  f"values('294277-1-9 4:00:54.775807');" \
                  f"insert {self.tb_name} values ('294277-1-9 4:00:54.4');" \
                  f"insert {self.tb_name} values('01-01-01 00:00:00');" \
                  f"insert {self.tb_name} values('9999-12-31 23:59:59');" \
                  f"insert {self.tb_name} values" \
                  f"('9999-12-31 23:59:59.9999999');" \
                  f"select * from {self.tb_name};"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertEqual(5, sql_res.count('INSERT 0 1'), '执行失败:' + text)
        msg_lis = ['0001-01-01 00:00:00+08:05:43', '294277-01-09 04:00:54+08',
                   '2001-01-01 00:00:00+08', '9999-12-31 23:59:59+08',
                   '10000-01-01 00:00:00+08']
        for msg in msg_lis:
            self.assertIn(msg, sql_res, '查询失败')
        self.assertIn(
            'ERROR:  timestamp out of range: "294277-01-09 04:00:55+08"',
            sql_res, '执行失败:' + text)

        text = '--step3:dolphin.sql_mode参数在非严格模式下，分别插入舍入后超最大值，' \
               '最大值;expect:插入舍入后超最大值有告警并返回零值，最大值成功--'
        self.log.info(text)
        sql_cmd = f"set dolphin.sql_mode = 'sql_mode_full_group," \
                  f"pipes_as_concat,ansi_quotes,no_zero_date," \
                  f"pad_char_to_full_length';" \
                  f"insert {self.tb_name} values" \
                  f"('294277-1-9 4:00:54.775807');" \
                  f"insert {self.tb_name} values('294277-1-9 4:00:54.4');" \
                  f"reset dolphin.sql_mode;" \
                  f"select * from {self.tb_name};"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('SET', sql_res, '执行失败:' + text)
        self.assertEqual(2, sql_res.count('INSERT 0 1'), '执行失败:' + text)
        self.assertIn(
            'WARNING:  timestamp out of range: "294277-01-09 04:00:55+08"',
            sql_res, '执行失败:' + text)
        self.assertIn('RESET', sql_res, '执行失败:' + text)
        msg_lis = ['0000-00-00 00:00:00+08:05:43', '294277-01-09 04:00:54+08']
        for msg in msg_lis:
            self.assertIn(msg, sql_res, '查询失败')

        text = '--step4:dolphin.sql_mode参数不含零值日期，插入含零值日期;expect:成功--'
        self.log.info(text)
        sql_cmd = f"set dolphin.sql_mode = 'sql_mode_full_group," \
                  f"pipes_as_concat,ansi_quotes,pad_char_to_full_length';" \
                  f"insert {self.tb_name} values('0000-00-00 01:00:00');" \
                  f"insert {self.tb_name} values('0000-00-00 11:00:00');" \
                  f"insert {self.tb_name} values('0000-00-00 00:00:00');" \
                  f"select * from {self.tb_name};" \
                  f"reset dolphin.sql_mode;"
        self.log.info(sql_cmd)
        sql_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                dbname=f'{self.db_name}')
        self.log.info(sql_res)
        self.assertIn('SET', sql_res, '执行失败:' + text)
        self.assertEqual(3, sql_res.count('INSERT 0 1'), '执行失败:' + text)
        msg_lis = ['0000-00-00 01:00:00+08:05:43',
                   ' 0000-00-00 11:00:00+08:05:43',
                   '0000-00-00 00:00:00+08:05:43']
        for msg in msg_lis:
            self.assertIn(msg, sql_res, '查询失败')
        self.assertIn('RESET', sql_res, '执行失败:' + text)

    def tearDown(self):
        text = '----step5:清理环境;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database {self.db_name};"
        self.log.info(sql_cmd)
        drop_res = self.sh_primary.execut_db_sql(sql_cmd,
                                                 dbname='postgres')
        self.log.info(drop_res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_res,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
